package com.weilan.ark3.sqlite;

import com.weilan.ark3.utils.StringUtils;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;

import java.io.*;
import java.nio.file.DirectoryStream;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;
import java.util.StringJoiner;

/**
 * sql操作工具类
 *
 * @author ruoyi
 */
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class SqlUtil {

    /**
     * 定义常用的 sql关键字
     */
    public static final String SQL_REGEX = "select |insert |delete |update |drop |count |exec |chr |mid |master |truncate |char |and |declare ";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static final String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value) {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value)) {
            throw new RuntimeException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value) {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value) {
        if (StringUtils.isEmpty(value)) {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords) {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1) {
                throw new RuntimeException("参数存在SQL注入风险");
            }
        }
    }

    /**
     * 创建脚本文件
     */
    public synchronized static int createScript(String sql, String folderPath) {
        String sqlPath = "C:\\db\\ARK3\\exportCsv.sql";
        String batPath = "C:\\db\\ARK3\\";
        try {
            BufferedWriter bw = new BufferedWriter(new FileWriter(sqlPath));
            bw.write(sql);
            bw.close();
            // Create the folder if it doesn't exist
            File folder = new File(folderPath);
            if (!folder.exists()) {
                if (folder.mkdirs()) {
                    System.out.println("Folder created successfully.");
                } else {
                    System.out.println("Failed to create folder.");
                    return 0; // Exit if the folder creation failed
                }
            } else {
                System.out.println("Folder already exists.");
            }

            String command = "cmd.exe /c sqlite3 " + batPath + "ark3.0.db < " + batPath + "exportCsv.sql ";
            Process process = Runtime.getRuntime().exec(command);
            // 获取批处理文件的输出流
            BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));

            // 输出批处理文件的执行结果
            String line;
            while ((line = reader.readLine()) != null) {
                System.out.println(line);
            }

            // 等待批处理文件执行完毕
            int exitCode = process.waitFor();

            // 输出执行结果
            if (exitCode != 0) {
                return 0;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 1;
    }

    /**
     * 创建SQL语句
     */
    public static String generateSql(List<String> tbList, String deviceId, String slaveId, String startTime, String endTime) {
        if (tbList == null || tbList.isEmpty()) {
            throw new IllegalArgumentException("Table list cannot be null or empty");
        }
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ");

        // Join table names with commas
        StringJoiner tableJoiner = new StringJoiner(", ");
        for (String table : tbList) {
            tableJoiner.add(table);
        }
        sql.append("(").append(tableJoiner.toString()).append(") WHERE ");

        if (tbList.size() > 1) {
            sql.append("(");
            for (int i = 1; i < tbList.size(); i++) {
                if (i > 1) {
                    sql.append(" AND ");
                }
                sql.append(tbList.get(0)).append(".date = ").append(tbList.get(i)).append(".date ")
                        .append("AND ").append(tbList.get(0)).append(".deviceId = ").append(tbList.get(i)).append(".deviceId ")
                        .append("AND ").append(tbList.get(0)).append(".slaveId = ").append(tbList.get(i)).append(".slaveId ");
            }
            sql.append(") AND ");
        }

        sql.append(tbList.get(0)).append(".deviceId = '").append(deviceId).append("'").append(" AND ")
                .append(tbList.get(0)).append(".slaveId = '").append(slaveId).append("'").append(" AND ")
                .append(tbList.get(0)).append(".date > '").append(startTime).append("'").append(" AND ")
                .append(tbList.get(0)).append(".date < '").append(endTime).append("'");
        return sql.toString();
    }

    /**
     * 删除文件
     */
    public static void deleteFilesInDirectory(String directoryPath) {
        Path dir = Paths.get(directoryPath);

        // 检查文件夹是否存在并且是一个目录
        if (!Files.exists(dir) || !Files.isDirectory(dir)) {
            return;
        }
        try (DirectoryStream<Path> stream = Files.newDirectoryStream(dir)) {
            for (Path entry : stream) {
                try {
                    Files.delete(entry);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建存储错误时导出SQL语句
     */
    public static String generateFaultSql(String tbName) {
        if (tbName == null) {
            throw new IllegalArgumentException("Table list cannot be null or empty");
        }
//        strftime('%Y-%m-%d %H:%M:%S', puc_info1_20240320.timestamp / 1000, 'unixepoch') AS formatted_datetime,*
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ");
        sql.append(tbName);

        return sql.toString();
    }
}
